package com.lgcns.andteam2.todaymenu.db;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.BaseColumns;
import android.text.TextUtils;
import android.util.Log;

import com.lgcns.andteam2.todaymenu.Constants;
import com.lgcns.andteam2.todaymenu.Util;
import com.lgcns.andteam2.todaymenu.info.PlaceInfo;
import com.lgcns.andteam2.todaymenu.info.TodayInfo;
import com.lgcns.andteam2.todaymenu.info.WeekInfo;
import com.lgcns.andteam2.todaymenu.item.TodayMenu;
import com.lgcns.andteam2.todaymenu.item.WeekDayMenu;

public class QueryHelper {
    private static final String TAG = QueryHelper.class.getSimpleName();

    /**
     * TB_PLACE 테이블 컬럼 정보
     */
    public static final class TbPlace implements BaseColumns {
        public static final String PLACE = "place";
    }
    
    /**
     * TB_DIETITIAN 테이블 컬럼 정보
     */
    public static final class TbDietitian implements BaseColumns {
        public static final String PLACE_ID = "place_id";
        public static final String DIETITIAN_NAME = "dietitian_name";
        public static final String EMAIL = "email";
    }
    
    /**
     * TB_MENU 테이블 컬럼 정보
     */
    public static final class TbMenu implements BaseColumns {
        public static final String PLACE_ID = "place_id";
        public static final String MENU_DATE = "menu_date";
        public static final String MENU_DAY = "menu_day";
        public static final String GUBUN_CODE = "gubun_code";
        public static final String GUBUN = "gubun";
        public static final String CORNER = "corner";
        public static final String MENU_NAME = "menu_name";
        public static final String CAL = "cal";
        public static final String SIDE_MENU01 = "side_menu_01";
        public static final String SIDE_MENU02 = "side_menu_02";
        public static final String SIDE_MENU03 = "side_menu_03";
        public static final String SIDE_MENU04 = "side_menu_04";
        public static final String SIDE_MENU05 = "side_menu_05";
        public static final String SIDE_MENU06 = "side_menu_06";
        public static final String SIDE_MENU07 = "side_menu_07";
        public static final String DESSERT = "dessert";
    }

    private QueryHelper() {
    }
    
    /**
     * 사업장 목록(placeId, place)을 반환한다.
     * @return
     */
    public static HashMap<String, String[]> getPlaceList(Context context) {
        HashMap<String, String[]> map = new HashMap<String, String[]>();
        MenuDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        Cursor c = null;

        try {
            String sql = "select _id, place from tb_place order by place";
            dbHelper = new MenuDBHelper(context);
            db = dbHelper.getReadableDatabase();
            Log.d(TAG, "getPlaceList() - sql : " + sql);
            c = db.rawQuery(sql, null);
            
            int count = c.getCount();
            String[] placeId = new String[count];
            String[] place = new String[count];
            
            for(int i = 0 ; c.moveToNext() ; i++) {
                placeId[i] = c.getString(c.getColumnIndex(TbPlace._ID));
                place[i] = c.getString(c.getColumnIndex(TbPlace.PLACE));
            }
            
            map.put("placeId", placeId);
            map.put("place", place);
        } finally {
            Util.close(c);
            Util.close(db);
            Util.close(dbHelper);
        }
        
        return map;
    }
    
    /**
     * 사업장 이름을 얻는다.
     * @param context
     * @param placeId
     * @return
     */
    public static String getPlace(Context context, String placeId) {
        MenuDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        Cursor c = null;

        String sql = "select _id, place from tb_place where _id = " + placeId;
        String place = null;
        
        try {
            dbHelper = new MenuDBHelper(context);
            db = dbHelper.getReadableDatabase();
            Log.d(TAG, "getPlace() - sql : " + sql);
            c = db.rawQuery(sql, null);
            
            if(c.moveToNext()) {
                place = c.getString(c.getColumnIndex(TbPlace.PLACE));
            }
        } finally {
            Util.close(c);
            Util.close(db);
            Util.close(dbHelper);
        }
        
        return place;
    }
    
    /**
     * 사업장의 담당자 이메일을 얻는다.
     * @param context
     * @param placeId
     * @return
     */
    public static String[] getDietitianEmail(Context context, int placeId) {
        MenuDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        Cursor c = null;

        String sql = "select email from tb_dietitian where place_id = " + placeId;
        String[] email = null;
        
        try {
            dbHelper = new MenuDBHelper(context);
            db = dbHelper.getReadableDatabase();
            Log.d(TAG, "getDietitianEmail() - sql : " + sql);
            c = db.rawQuery(sql, null);
            
            email = new String[c.getCount()];
            
            for(int i = 0 ; c.moveToNext() ; i++) {
                email[i] = c.getString(c.getColumnIndex(TbDietitian.EMAIL));
            }
        } finally {
            Util.close(c);
            Util.close(db);
            Util.close(dbHelper);
        }
        
        return email;
    }
    
    /**
     * 오늘메뉴 조회 결과를 오늘 메뉴정보(TodayMenu) ArrayList에 담는다.
     * @param context
     * @param menuList
     */
    public static void populateTodayMenuData(Context context, ArrayList<TodayMenu> menuList) {
        MenuDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        Cursor c = null;

        String placeId = PlaceInfo.getInstance(context).getPlaceId();
        TodayInfo todayInfo = TodayInfo.getTodayInfo(context);
        String date = todayInfo.getDate();
        String gubunCode = todayInfo.getGubunCode();
        
        String sql = 
            "select _id, place_id, menu_date, menu_day, gubun_code, \n" +
            "       gubun, corner, menu_name, cal, side_menu_01, \n" +
            "       side_menu_02, side_menu_03, side_menu_04, side_menu_05, \n" +
            "       side_menu_06, side_menu_07, dessert \n" +
            "  from tb_menu \n" +
            " where place_id = " + placeId +
            "   and menu_date = '" + date + "' \n" +
            "   and gubun_code = '" + gubunCode + "' \n" +
            "order by corner";
        
        try {
            dbHelper = new MenuDBHelper(context);
            db = dbHelper.getReadableDatabase();
            Log.d(TAG, "populateTodayMenuData() - sql : " + sql);
            c = db.rawQuery(sql, null);
    
            int idxId = c.getColumnIndex(TbMenu._ID);
            int idxPlaceId = c.getColumnIndex(TbMenu.PLACE_ID);
            int idxMenuDate = c.getColumnIndex(TbMenu.MENU_DATE);
            int idxMenuDay = c.getColumnIndex(TbMenu.MENU_DAY);
            int idxGubunCode = c.getColumnIndex(TbMenu.GUBUN_CODE);
            int idxGubun = c.getColumnIndex(TbMenu.GUBUN);
            int idxCorner = c.getColumnIndex(TbMenu.CORNER);
            int idxMenuName = c.getColumnIndex(TbMenu.MENU_NAME);
            int idxCal = c.getColumnIndex(TbMenu.CAL);
            int idxSideMenu01 = c.getColumnIndex(TbMenu.SIDE_MENU01);
            int idxSideMenu02 = c.getColumnIndex(TbMenu.SIDE_MENU02);
            int idxSideMenu03 = c.getColumnIndex(TbMenu.SIDE_MENU03);
            int idxSideMenu04 = c.getColumnIndex(TbMenu.SIDE_MENU04);
            int idxSideMenu05 = c.getColumnIndex(TbMenu.SIDE_MENU05);
            int idxSideMenu06 = c.getColumnIndex(TbMenu.SIDE_MENU06);
            int idxSideMenu07 = c.getColumnIndex(TbMenu.SIDE_MENU07);
            int idxDessert = c.getColumnIndex(TbMenu.DESSERT);
    
            menuList.clear();
            
            for(int i = 0 ; c.moveToNext() ; i++) {
                TodayMenu menu = new TodayMenu();
                menu.setId(c.getInt(idxId));
                menu.setPlaceId(c.getInt(idxPlaceId));
                menu.setMenuDate(c.getString(idxMenuDate));
                menu.setMenuDay(c.getString(idxMenuDay));
                menu.setGubunCode(c.getString(idxGubunCode));
                menu.setGubun(c.getString(idxGubun));
                menu.setCorner(c.getString(idxCorner));
                menu.setMenuName(c.getString(idxMenuName));
                menu.setCal(c.getInt(idxCal));
                menu.setSideMenu01(c.getString(idxSideMenu01));
                menu.setSideMenu02(c.getString(idxSideMenu02));
                menu.setSideMenu03(c.getString(idxSideMenu03));
                menu.setSideMenu04(c.getString(idxSideMenu04));
                menu.setSideMenu05(c.getString(idxSideMenu05));
                menu.setSideMenu06(c.getString(idxSideMenu06));
                menu.setSideMenu07(c.getString(idxSideMenu07));
                menu.setDessert(c.getString(idxDessert));
                menuList.add(menu);
            }
        } finally {
            Util.close(c);
            Util.close(db);
            Util.close(dbHelper);
        }
    }
    
    /**
     * 주간메뉴 조회 결과를 주간메뉴정보(WeekMenu) ArrayList에 담는다.
     * @param context
     * @param menuList
     */
    public static void populateWeekDayMenuData(Context context, ArrayList<WeekDayMenu> menuList) {
        MenuDBHelper dbHelper = null;
        SQLiteDatabase db = null;
        Cursor c = null;

        String placeId = PlaceInfo.getInstance(context).getPlaceId();
        WeekInfo weekInfo = WeekInfo.getWeekInfo(context);
        
        String sql = 
            "select _id, menu_date, menu_day, gubun_code, corner, menu_name \n" + 
            "  from tb_menu \n" +
            " where place_id = " + placeId + "\n" +
            "   and menu_date >= " + weekInfo.getStartDate() + "\n" +
            "   and menu_date <= " + weekInfo.getEndDate() + "\n" +
            " order by menu_date, gubun_code, corner";
        
        try {
            dbHelper = new MenuDBHelper(context);
            db = dbHelper.getReadableDatabase();
            Log.d(TAG, "populateWeekDayMenuData() - sql : " + sql);
            c = db.rawQuery(sql, null);
    
            int idxMenuDate = c.getColumnIndex(TbMenu.MENU_DATE);
            int idxMenuDay = c.getColumnIndex(TbMenu.MENU_DAY);
            int idxGubunCode = c.getColumnIndex(TbMenu.GUBUN_CODE);
            int idxCorner = c.getColumnIndex(TbMenu.CORNER);
            int idxMenuName = c.getColumnIndex(TbMenu.MENU_NAME);
    
            menuList.clear();
            
            // 데이터는 날짜, 구분, 코너로 정렬되어 있음
            int gubunIndex = 0;
            for(int i = 0 ; c.moveToNext() ; i++) {
                WeekDayMenu menuInfo = new WeekDayMenu();
                menuInfo.setDate(c.getString(idxMenuDate));
                menuInfo.setDay(c.getString(idxMenuDay));
                
                while(true) {
                    String date = (c.getString(idxMenuDate));
                    if(!menuInfo.getDate().equals(date)) { // 다음 날짜 이면
                        c.moveToPrevious();
                        break;
                    }
                    
                    String gubunCode = c.getString(idxGubunCode);
                    gubunIndex = checkGubunIndex(gubunCode);
    
                    String corner = c.getString(idxCorner);
                    String menuName = null;
                    if(TextUtils.isEmpty(corner)) {
                        menuName = c.getString(idxMenuName);
                    } else {
                        menuName = "[" + corner + "] " + c.getString(idxMenuName);
                    }
                    
                    menuInfo.addMenuName(gubunIndex, menuName + "\n");
                    
                    if(!c.moveToNext()) { // 다음 결과가 없으면
                        break;
                    }
                }
                
                menuList.add(menuInfo);
            }
        } finally {
            Util.close(c);
            Util.close(db);
            Util.close(dbHelper);
        }
    }
    
    /**
     * gubun index를 넘겨준다.
     * @param gubunCode
     * @return
     */
    private static int checkGubunIndex(String gubunCode) {
        int index = 0;
        if(Constants.BREAKFAST_GUBUN_CODE.equals(gubunCode)) {
            index = 0;
        } else if(Constants.LUNCH_GUBUN_CODE .equals(gubunCode)) {
            index = 1;
        } else if(Constants.DINNER_GUBUN_CODE .equals(gubunCode)) {
            index = 2;
        }
        return index;
    }
}
